package de.nroz.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import de.nroz.model.Artikel;
import de.nroz.model.ArtikelModel;
import de.nroz.model.DBConstants;

public class DBUtil
{
	Connection c = null;

	private final static DBUtil instance = new DBUtil();

	private DBUtil()
	{
	}

	public static DBUtil getInstance()
	{
		return instance;
	}

	public void openConnection()
	{
		try
		{
			c = DriverManager.getConnection(
					"jdbc:hsqldb:file:" + DBConstants.dbName,
					DBConstants.dbUser,
					DBConstants.dbPassword);
			// for test only
			//			 dropTables();
			Statement stmt = c.createStatement();
			// pruefen ob die Tabellen existieren
			stmt.execute("select count(*) from artikel");
			stmt.execute("select count(*) from bilder");
			stmt.close();
		}
		catch (SQLException e)
		{
			if (e.getErrorCode() == -5501)
			{
				try
				{
					createTables();
				}
				catch (SQLException e1)
				{
					e1.printStackTrace();
				}
			}
			else
			{
				e.printStackTrace();
			}
		}
	}

	public void closeConnection()
	{
		try
		{
			Statement stmt = c.createStatement();
			stmt.execute("SHUTDOWN");
			stmt.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

	}

	/**
	 * CREATE [TEMP] [CACHED|MEMORY|TEXT] TABLE name ( ColumnDefinition [, ...]
	 * )
	 * 
	 * ColumnDefinition: Column DataType [ [NOT] NULL] [PRIMARY KEY] DataType: {
	 * INTEGER | DOUBLE | VARCHAR | DATE | TIME |... }
	 * 
	 * @throws SQLException
	 */
	private void createTables() throws SQLException
	{
		StringBuilder artikelSql = new StringBuilder();
		artikelSql.append("CREATE TABLE ").append(DBConstants.TABLE_ARTIKEL).append("(");
		artikelSql.append(DBConstants.COL_ARTID).append(" BIGINT IDENTITY, ");
		artikelSql.append(DBConstants.COL_ARTNR).append(" VARCHAR(4), ");
		artikelSql.append(DBConstants.COL_ARTIKEL).append(" VARCHAR(100), ");
		artikelSql.append(DBConstants.COL_ORT).append(" VARCHAR(2), ");
		artikelSql.append(DBConstants.COL_EK_R_PREIS).append(" DECIMAL(8,2), ");
		artikelSql.append(DBConstants.COL_EK_FA_PREIS).append(" DECIMAL(8,2), ");
		artikelSql.append(DBConstants.COL_VK_PREIS).append(" DECIMAL(8,2), ");
		artikelSql.append(DBConstants.COL_EK_DATUM).append(" DATE, ");
		artikelSql.append(DBConstants.COL_VK_DATUM).append(" DATE, ");
		artikelSql.append(DBConstants.COL_BESCHREIBUNG).append(" VARCHAR(400), ");
		artikelSql.append(DBConstants.COL_BEZAHLT).append(" BOOLEAN, ");
		artikelSql.append(DBConstants.COL_KANAL).append(" VARCHAR(20), ");
		artikelSql.append(")");

		StringBuilder bilderSql = new StringBuilder();
		bilderSql.append("CREATE TABLE ").append(DBConstants.TABLE_BILDER).append("(");
		bilderSql.append(DBConstants.COL_BILDID).append(" BIGINT IDENTITY, ");
		bilderSql.append(DBConstants.COL_ARTID).append(" BIGINT NOT NULL FOREIGN KEY REFERENCES ");
		bilderSql.append(DBConstants.TABLE_ARTIKEL).append("(");
		bilderSql.append(DBConstants.COL_ARTID).append("), ");
		bilderSql.append(DBConstants.COL_BILD).append(" BLOB");
		bilderSql.append(")");

		Statement stmt = c.createStatement();
		stmt.execute(artikelSql.toString());
		stmt.execute(bilderSql.toString());
		stmt.close();

	}

	private void dropTables() throws SQLException
	{
		Statement stmt = c.createStatement();
		stmt.execute("DROP TABLE BILDER");
		stmt.execute("DROP TABLE ARTIKEL");
		stmt.close();
	}
	
	public void insertOrUpdate(Artikel art)
	{
		if(art.getArtId() == -1)
		{
			insert(art);
		}
		else
		{
			update(art);
		}
	}

	private void insert(Artikel art)
	{
		try
		{

			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO ARTIKEL(");
			sql.append(DBConstants.COL_ARTNR).append(",");
			sql.append(DBConstants.COL_ARTIKEL).append(",");
			sql.append(DBConstants.COL_ORT).append(",");
			sql.append(DBConstants.COL_EK_R_PREIS).append(",");
			sql.append(DBConstants.COL_EK_FA_PREIS).append(",");
			sql.append(DBConstants.COL_VK_PREIS).append(",");
			sql.append(DBConstants.COL_EK_DATUM).append(",");
			sql.append(DBConstants.COL_VK_DATUM).append(",");
			sql.append(DBConstants.COL_BESCHREIBUNG).append(",");
			sql.append(DBConstants.COL_BEZAHLT).append(",");
			sql.append(DBConstants.COL_KANAL);
			sql.append(") VALUES (?,?,?,?,?,?,?,?,?,?,?)");

			int idx = 1;
			PreparedStatement pstmt = c.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(idx++, art.getArtNr());
			pstmt.setString(idx++, art.getArtikel());
			pstmt.setString(idx++, art.getOrt());
			pstmt.setBigDecimal(idx++, art.getEkRPreis());
			pstmt.setBigDecimal(idx++, art.getEkFaPreis());
			pstmt.setBigDecimal(idx++, art.getVkPreis());
			pstmt.setDate(idx++, art.getEkDatum() != null ? new java.sql.Date(art.getEkDatum().getTime()) : null);
			pstmt.setDate(idx++, art.getVkDatum() != null ? new java.sql.Date(art.getVkDatum().getTime()) : null);
			pstmt.setString(idx++, art.getBeschreibung());
			pstmt.setBoolean(idx++, art.isBezahlt());
			pstmt.setString(idx++, art.getKanal());
			pstmt.executeUpdate();
			ResultSet generatedKeys = pstmt.getGeneratedKeys();
			if (generatedKeys.next())
			{
				System.out.println(generatedKeys.getLong(1));
				art.setArtId(generatedKeys.getLong(1));
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	private boolean update(Artikel art)
	{
		boolean commited = false;
		try
		{
			StringBuilder sql = new StringBuilder();
			sql.append("UPDATE ARTIKEL SET ");
			sql.append(DBConstants.COL_ARTNR).append(" =?, ");
			sql.append(DBConstants.COL_ARTIKEL).append(" =?, ");
			sql.append(DBConstants.COL_ORT).append(" =?, ");
			sql.append(DBConstants.COL_EK_R_PREIS).append(" =?, ");
			sql.append(DBConstants.COL_EK_FA_PREIS).append(" =?, ");
			sql.append(DBConstants.COL_VK_PREIS).append(" =?, ");
			sql.append(DBConstants.COL_EK_DATUM).append(" =?, ");
			sql.append(DBConstants.COL_VK_DATUM).append(" =?, ");
			sql.append(DBConstants.COL_BESCHREIBUNG).append(" =?, ");
			sql.append(DBConstants.COL_BEZAHLT).append(" =?, ");
			sql.append(DBConstants.COL_KANAL).append(" =?");
			sql.append(" WHERE ").append(DBConstants.COL_ARTID).append("=?");

			int idx = 1;
			PreparedStatement pstmt = c.prepareStatement(sql.toString());
			pstmt.setString(idx++, art.getArtNr());
			pstmt.setString(idx++, art.getArtikel());
			pstmt.setString(idx++, art.getOrt());
			pstmt.setBigDecimal(idx++, art.getEkRPreis());
			pstmt.setBigDecimal(idx++, art.getEkFaPreis());
			pstmt.setBigDecimal(idx++, art.getVkPreis());
			pstmt.setDate(idx++, art.getEkDatum() != null ? new java.sql.Date(art.getEkDatum().getTime()) : null);
			pstmt.setDate(idx++, art.getVkDatum() != null ? new java.sql.Date(art.getVkDatum().getTime()) : null);
			pstmt.setString(idx++, art.getBeschreibung());
			pstmt.setBoolean(idx++, art.isBezahlt());
			pstmt.setString(idx++, art.getKanal());
			pstmt.setLong(idx++, art.getArtId());
			pstmt.executeUpdate();
			commited = true;
		}
		catch (SQLException e)
		{
			commited = false;
			e.printStackTrace();
		}

		return commited;
	}

	public void delete(Artikel art)
	{
		try
		{

			StringBuilder sql = new StringBuilder();
			sql.append("DELETE FROM ").append(DBConstants.TABLE_ARTIKEL);
			sql.append(" WHERE ").append(DBConstants.COL_ARTID).append(" = ?");

			PreparedStatement pstmt = c.prepareStatement(sql.toString());

			int idx = 1;
			pstmt.setLong(idx++, art.getArtId());
			pstmt.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public ArtikelModel getList()
	{
		ArtikelModel list = new ArtikelModel();
		try
		{
			Statement stmt = c.createStatement();
			ResultSet rs = stmt
					.executeQuery("select ARTID, ARTNR, ARTIKEL, ORT, EK_R_PREIS, EK_FA_PREIS, VK_PREIS, EK_DATUM, VK_DATUM, BESCHREIBUNG, BEZAHLT, KANAL from artikel");
			Artikel art = null;
			while (rs.next())
			{
				art = new Artikel();
				int idx = 1;
				art.setArtId(rs.getLong(idx++));
				art.setArtNr(rs.getString(idx++));
				art.setArtikel(rs.getString(idx++));
				art.setOrt(rs.getString(idx++));
				art.setEkRPreis(rs.getBigDecimal(idx++));
				art.setEkFaPreis(rs.getBigDecimal(idx++));
				art.setVkPreis(rs.getBigDecimal(idx++));
				art.setEkDatum(rs.getDate(idx++));
				art.setVkDatum(rs.getDate(idx++));
				art.setBeschreibung(rs.getString(idx++));
				art.setBezahlt(rs.getBoolean(idx++));
				art.setKanal(rs.getString(idx++));
				list.add(art);
			}
			rs.close();
			stmt.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
